为什么要 “分库分表”
分库分表的目的就在于此:减小数据库的负担,提高数据库的效率,缩短查询时间。另外,因为分库分表这种改造是可控的,底层还是基于RDBMS,因此整个数据库的运维体系以及相关基础设施都是可重用的。
随着互联网产业的蓬勃发展,在互联网应用上产生的数据也是与日俱增。产生大量的交易记录和行为记录,它们的存放和分析是我们需要面对的问题。数据库数据会随着业务的发展而不断增多,因此数据操作,如增删改查的开销也会越来越大。再加上物理服务器的资源有限(CPU、磁盘、内存、IO 等)。最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。换句话说需要合理的数据库架构来存放不断增长的数据,当一张表的数据达到几千万时,查询一次所花的时间会变长。这时候,如果有联合查询的话,可能会卡死在那儿,甚至把系统给拖垮。
目前我们系统将近20亿数据,每张表最大的接近600w条/表,每条数据大约3k,每个表将近1.5G的数据。查询经常超时,单条SQL执行count(*)查询时间达到了最大260ms,0.26s(标准是超过0.1s的数据为慢SQL)。
为了说明我们为什么要分库分表,我们看一下sql的执行过程。
mysql执行一条sql的过程如下:
1、收到sql
2、把sql放到排队队列中
3、执行sql
4、返回结果
在这个执行过程中最花时间的地方在于:
1.排队等待的时间,
2.sql的执行时间。
如果有2个sql都要同时修改同一张表的同一条数据,mysql对这种情况的处理是:一种是表锁定(MyISAM存储引擎),一个是行锁定(InnoDB存储引擎)。
表锁定表示其他操作都不能对这张表进行操作,必须等当前对表的操作完才行。行锁定也一样,别的sql必须等这条数据操作完了,其他人才能对这条数据进行操作。
如果数据太多,一次执行的时间太长,等待的时间就越长,这也是我们为什么要分表的原因。
分库分表术语:
读写分离: 不同的数据库,同步相同的数据,分别只负责数据的读和写;
分区:指定分区列表达式,把记录拆分到不同的区域中(必须是同一服务器,可以是不同硬盘),应用看来还是同一张表,没有变化;
分库:一个系统的多张数据表,存储到多个数据库实例中;
分表: 对于一张多行(记录)多列(字段)的二维数据表,又分两种情形:
垂直分表: 竖向切分,不同分表存储不同的字段,可以把不常用或者大容量、或者不同业务的字段拆分出去;水平分表(最复杂): 横向切分,按照特定分片算法,不同分表存储不同的记录。在实际生产中,通常的进化过程是:单库单表->单库多表->多库多表;;分区->分表->分库(垂直分库 - 水平分库 - 读写分离)
单库单表
单库单表是最常见的数据库设计,例如,有一张订单表(order)放在数据库中,所有的订单都可以在order表中查到。
单库多表
随着订单数量的增加,order表的数据量会越来越大,当数据量达到一定程度的时候,对order表的查询会变慢,从而影响整个DB的性能。
另外,随着需求的迭代,如果增加添加一列的时候,mysql会锁表,期间所有的读写操作只能等待,别无他法。
这时候,可以将order进行水平的切分,产生多个表结构完全一样的order表。比如:order_01,order_02….,order_n,那么order_01+order_02+order_n的数据是一份完整的订单数据。
这个水平切分,简单的做法如:
按数量切分,1~1000的存在第一张表,1001~2000存在第二张表;
按时间切分,比如:2019年1月份存在第一张表,2019年2月份存在第二张表;还可以按照id的哈希值进行切分,等等等等
多库多表
随着数据量增加,单台数据库的硬件存储不够了,并且,随着查询量的增加,单台数据库服务器已经没办法支撑。这时候就需要对数据库进行水平区分。
比如按地区分库,一个省份在一个物理数据库等等
任何事情都有两面性,分库分表也不例外,如果采用分库分表,会引入新的的问题
1.分布式事务问题
做了垂直分库或者水平分库以后,就必然会涉及到跨库执行SQL的问题,就会引发互联网界的老大难问题-“分布式事务”。那么要如何解决这个问题呢?
使用分布式事务中间件使用MySQL自带的针对跨库的事务一致性方案(XA),不过性能要比单库的慢10倍左右。能否避免掉跨库操作(比如将用户和商品放在同一个库中)
2.跨库join的问题
分库分表后,表之间的关联操作将受到限制,就无法join位于不同分库的表,也无法join分表粒度不同的表, 结果原本一次查询能够完成的业务,可能需要多次查询才能完成。
那么要如何解决这个问题呢?
简单的解决方法:
全局表:基础数据,所有库都拷贝一份。字段冗余:把需要join的字段冗余在各个表中,这样有些字段就不用join去查询了。系统层组装:应用端先分别查询出所有复核条件的,然后在应用端组装起来,类似于一个mapreduce的过程(较复杂)。
3.横向扩容的问题
当我们使用哈希取模做分表的时候,针对数据量的递增,可能需要动态的增加表,此时就需要考虑数据迁移的问题。
原来使用的是hash后对8进行取模,那么,数据是均分在8个表(库)上。
如果8个表不够的时候,我们要扩展到16个表,这时候,我们hash后对16取模,新数据是没有问题的,旧数据就会发生错乱。
如果哈希后是9,那么,原来我们对8取模后,是1,会到表1进行查询;但是,现在我们是对16取模,那么是到表9进行查询的,而这个数据在表9又不存在,因此,就会找不到数据了
4.结果集合并、排序的问题
因为我们是将数据分散存储到不同的库、表里的,当我们查询指定数据列表时,数据来源于不同的子库或者子表,就必然会引发结果集合并、排序的问题。
如果每次查询都需要排序、合并等操作,性能肯定会受非常大的影响。